import sys
import os
import paramiko
import time
import io
import subprocess
import datetime
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from threading import Lock, Thread
import sys
import subprocess
import datetime
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from datetime import timedelta , date
import psycopg2

def createConn():
    funList = []
    retDic = {}
    today = date.today()
    dates = [today - timedelta(days=i) for i in range(7)]
    dates = list(reversed(dates))
    formattedDates = [d.strftime('%Y-%m-%d') for d in dates]

    conn = psycopg2.connect(database="yat_record", user="yat_user", password="Huawei@123", host="20.20.20.155", port="26002")
    curConn = conn.cursor()
    #获取功能模块：
    selectExampleSql = "SELECT CaseName,CaseOperation,Owner from CaseDetailTable where FunName = %s"
    selectExampleExcuteSql = "SELECT ExcuteData,State from caseexcutetable where FunName = %s and CaseName = %s and ExcuteData >= %s and ExcuteData <= %s"
    curConn.execute("SELECT DISTINCT FunName from CaseDetailTable");

    rows = curConn.fetchall()
    for row in rows:
      funList.append(row[0])
    for funName in funList:
      replaceSelectExampleSql = (funName,)
      curConn.execute(selectExampleSql, replaceSelectExampleSql)
      tempDic1 = {}
      rows = curConn.fetchall()
      for row in rows:
        tempDic2 = {}
        tempDic2['operation'] = row[1]
        tempDic2['hoster'] = row[2]
        # 开始查询每日执行用例情况
        replaceSelectExampleExcuteSql = (funName, row[0], formattedDates[0], formattedDates[6])
        curConn.execute(selectExampleExcuteSql, replaceSelectExampleExcuteSql)
        excuteRows = curConn.fetchall()
        isOk = 0
        caseState = 'good'
        for d in formattedDates:
            tempDic2[d] = '-'
        for excuteRow in excuteRows:
          #if isinstance(excuteRow[0], str):
           #   date_obj = datetime.strptime(excuteRow[0], '%Y-%m-%d')
          #else:
           #   date_obj = excuteRow[0]  # 假设它已经是日期时间对象

          # 然后使用 strftime 方法
         # tempDic2[date_obj.strftime('%Y-%m-%d')] = excuteRow[1]
          tempDic2[excuteRow[0].strftime('%Y-%m-%d')] = excuteRow[1]
          if excuteRow[1] == 'ok':
            isOk += 1;
        if len(excuteRows) == 0:
          caseState = 'notEnough'
        elif isOk/len(excuteRows) <0.5:
          caseState = 'bad'
        elif isOk/len(excuteRows) <0.8:
          caseState = 'normal'
        tempDic2['state'] = caseState
        tempDic1[row[0]] = tempDic2
      retDic[funName] = tempDic1

    return retDic, formattedDates
    
def buildHistroyTable(data,sevenDate):
    '''
        整体模式如下所示：
        - 功能名称：
            - 用例名称：
                - 用例功能
                - 各个日期：
                    - 是否成功
                - 责任人：
    '''
    finalData = f''''''
    retHalf = f'''
        <table id="sevenDaysDetailTable" border="1" onload="func_load()">
            <tr bgcolor="#d9ffcc">
                <th style="width: 9%">功能模块名称</th>
                <th style="width: 9%">用例名称</th>
                <th style="width: 20%">用例功能</th>
                <th style="width: 3%">{sevenDate[0]}</th>
                <th style="width: 3%">{sevenDate[1]}</th>
                <th style="width: 3%">{sevenDate[2]}</th>
                <th style="width: 3%">{sevenDate[3]}</th>
                <th style="width: 3%">{sevenDate[4]}</th>
                <th style="width: 3%">{sevenDate[5]}</th>
                <th style="width: 3%">{sevenDate[6]}</th>
                <th style="width: 3%">质量点灯</th>
                <th style="width: 5%">责任人</th>
            </tr>
    '''
    for funName, funDic in data.items():
      # 遍历所有funName，开始打印；
      isFirst = True
      length = len(funDic)
      for exampleName, exampleDic in funDic.items():
        tempData = f''''''
        if isFirst:
          isFirst = False
          tempData += f'''<tr><td rowspan="{length}" style='text-align: center; vertical-align: middle;'>{funName}</td>'''
        else:
           tempData += f'''<tr>'''
        tempData += f'''<td>{exampleName}</td><td>{exampleDic['operation']}</td>'''
        tempStr = f''''''
        for dayDate in sevenDate:
          if exampleDic[dayDate] == 'ok':
            tempStr = f'''<span style='color: green;'>通过</span>'''
          elif exampleDic[dayDate] == 'er':
            tempStr = f'''<span style='color: red;'>异常</span>'''
          else:
            tempStr = f'''<span style='color: black;'>-</span>'''
          tempData += f'''<td>{tempStr}</td>'''
        if exampleDic['state'] == 'good':
          tempStr = f'''<span style='color: green;'><strong>良好</strong></span>'''
        elif exampleDic['state'] == 'bad':
          tempStr = f'''<span style='color: red;'><strong>较差</strong></span>'''
        elif exampleDic['state'] == 'normal':
          tempStr = f'''<span style='color: orange;'><strong>一般</strong></span>'''
        else:
          tempStr = f'''<span style='color: black;'><strong>数据不足</strong></span>'''
        tempData += f'''<td>{tempStr}</td><td>{exampleDic['hoster']}</td></tr>'''
        finalData += tempData
    ret = retHalf + f'''
                  {finalData}
        </table>
    '''
    return ret

def bulidTable(result_path):
    table_row = ''
    ok_num = 0
    er_num = 0
    map_dict = dict()
    ss = dict()
    with open(result_path + "/result.out", 'r') as f:
        lines = f.readlines()
        for line in lines:
            tempList = line.split(' ')
            funName = tempList[0]
            if tempList[1] ==  'ok':
                funResult = "<span style='color: green;'><strong>用例正常</strong></span>"
            elif tempList[1] == 'er':
                funResult = "<span style='color: red;'><strong>用例异常</strong></span>"
            else:
                funResult = "<span style='color: orange;'><strong>用例挂起</strong></span>"
            funDetailList = tempList[2].split('/')
            allNum =  int(funDetailList[1])
            okNum = int(funDetailList[0])
            table_row += '<tr>'
            table_row += f'''<td>{funName}</td>
                <td>{funResult}</td>
                <td>{allNum}</td>
                <td>{okNum}</td>
                <td>{allNum-okNum}</td>
            </tr>
            '''
    return table_row

def bulidTableNum(result_path):
    table_row = ''
    ok_num = 0
    all_num = 0
    ss = dict()
    with open(result_path + "/result.out", 'r') as f:
        lines = f.readlines()
        for line in lines:
            tempList = line.split(' ')
            funName = tempList[0]
            funDetailList = tempList[2].split('/')
            all_num +=  int(funDetailList[1])
            ok_num += int(funDetailList[0])
        table_row += '<tr>'
        table_row += f'''<td>{all_num}</td>
            <td>{ok_num}</td>
            <td>{all_num-ok_num}</td>
        </tr>
        '''
    return table_row
def get_install_last_line(result_path):
    with open(result_path + "/install.log", 'r') as file:
        lines = file.readlines()
        if lines:
            return lines[-1].strip()  # 返回最后一行并去除末尾的换行符
        else:
            return "没有获取到数据库版本信息"  # 文件为空时返回空字符串

def generateHTML(data,finalResult,gaussdb_v):
    retDic, timeList = createConn()
    detailData = buildHistroyTable(retDic, timeList)
    mail_msg = f'''<!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">

    <title>openGauss全量用例适配资源池化测试结果</title>

    <style>
        table {{
            border-collapse: collapse;
            width: 100%;
            border-spacing: 0;
            table-layout: fixed;
            word-wrap: break-all;
        }}
        
        table th {{
            text-align: center;
            word-break: keep-all;
            white-space:nowrap;
        }}

        table td {{
            word-break: keep-all;
            white-space:nowrap;
            text-align: center;
            padding: 10px 30px;
        }}
    </style>
    </head>
    <body>
        <h2>今日资源池化开发者用例连跑结果：</h2>
        <table id="resultTable" border="1" onload="func_load()">
            <tr bgcolor="#d9ffcc">
                <td style="width: 20%">测试项目名称</td>
                <td style="width: 20%">测试总体情况</td>
                <td style="width: 9%">用例总数</td>
                <td style="width: 9%">成功</td>
                <td style="width: 9%">失败</td>
            </tr>
            {data}
        </table>
        <h2>今日资源池化开发者用例连跑结果统计：</h2>
        <table id="resultTable" border="1" onload="func_load()">
        <tr bgcolor="#d9ffcc">
                <td style="width: 20%">用例总数合计</td>
                <td style="width: 20%">成功用例总数合计</td>
                <td style="width: 20%">失败用例总数合计</td>
            </tr>
            {finalResult}
        </table>
        <h2>今日资源池化开发者数据库版本信息：</h2>
                <table id="resultTable" border="1" onload="func_load()">
            <tr bgcolor="#d9ffcc">
                <td style="width: 40%"></td>
            </tr>
            {gaussdb_v}
        </table>
        <h2>资源池化开发者用例七日连跑汇总：</h2>
        <p><span style='color: red;'><strong>请缩放页面至合适大小</strong></span></p>
        <p><strong>七天联跑质量说明：以七天为周期，用例失败一次及以下为良好，失败半数以下为较差，剩余为一般</strong></p>
        {detailData}
        <p><strong><a href="https://gitee.com/muyulinzhong/pipline-result-show/blob/master/TodayResult.md">报告详细地址</a></strong></p>
    </body>
    </html>
    '''
    return mail_msg

def ssh_connect(ip, username, pwd):
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(ip, 22, username, pwd, timeout=600, banner_timeout=60)
    return ssh

def ssh_close(ssh):
    ssh.close()

def ssh_run(ssh, cmd, ispwd=False, is_print=True):
    buffer_size = 1024 * 1024
    if not ispwd:
        print(f'执行命令: {cmd.strip()}')
    channel = ssh.get_transport().open_session()
    channel.exec_command(cmd)
    buffer = _Buffer()
    cnt = 0
    return_res = None
    try:
        while True:
            if cnt >= 12000:
                print(f'ssh_run执行超时')
                break
            while channel.recv_ready():
                stdout = channel.recv(buffer_size)
                stdout_reader = _read_to(stdout.decode('Utf-8'),
                                                buffer)
                stdout_reader.join()
            while channel.recv_stderr_ready():
                stderr = channel.recv_stderr(buffer_size)
                stderr_reader = _read_to(stderr.decode('Utf-8'),
                                                buffer)
                stderr_reader.join()
            if channel.exit_status_ready():
                if is_print:
                    print(f'执行完成')
                break
            time.sleep(1)
            cnt += 1
        return_res = buffer.getvalue()
        if is_print:
            print(f'{return_res}\n')
    except Exception as e:
        print(f'Error: {str(e)}')
    finally:
        buffer.close()
    return return_res.strip()

def _read_to(stream, buffer):
    def _read_handle():
        buffer.write(stream)

    thread = Thread(target=_read_handle)
    thread.start()
    return thread

class _Buffer:
    def __init__(self):
        self._io = io.StringIO()
        self._lock = Lock()

    def write(self, data):
        try:
            self._lock.acquire()
            self._io.write(data)
        finally:
            self._lock.release()

    def getvalue(self):
        return self._io.getvalue()

    def close(self):
        self._io.close()

def sendEmail(path):
    mail_msg = ""
    with open(path + "/mail_msg.html", 'r') as f:
        lines = f.readlines()
    for line in lines:
        mail_msg += str(line)
    mail_host = 'smtp.exmail.qq.com'
    mail_user = 'issuemanager@public.opengauss.org'
    mail_pass = '9x5tfG4uBz5Km3Dy'

    sender = 'issuemanager@public.opengauss.org'
    to_receivers = ["songshixuan@huawei.com","joshua.chen@huawei.com","wanfang5@h-partners.com","zb.zhoubin@huawei.com","f.fengwei@huawei.com","chendong76@huawei.com","zhengxue6@huawei.com","zhoucong42@huawei.com","guozhe7@huawei.com","jingtao17@h-partners.com","hejiahuan11@h-partners.com","zhangyuemeng2@huawei.com","huzhengchao4@huawei.com","zhenjinyang@huawei.com","zhongchengzhi4@huawei.com","chenzhikai11@huawei.com","liuzhanfeng2@huawei.com"]
    #to_receivers = ["songshixuan@huawei.com","zhenjinyang@huawei.com","huzhengchao4@huawei.com"]
    cc_receivers = []

    receivers = to_receivers + cc_receivers
    message = MIMEText(mail_msg, 'html', 'utf-8')
    # message['From'] = Header(sender, 'utf-8')  # 发送者
    message['To'] = Header(",".join(to_receivers))  # 接收者
    message['Cc'] = Header(",".join(cc_receivers))
    shijian = datetime.datetime.now().strftime('%Y-%m-%d')
    subject = "【请阅】资源池化Yat每日连跑(" + shijian + ")"
    message['Subject'] = Header(subject, 'utf-8')
    try:
        smtp_obj = smtplib.SMTP()
        smtp_obj.connect(mail_host)
        smtp_obj.login(mail_user, mail_pass)
        smtp_obj.sendmail(sender, receivers, message.as_string())
        print("邮件发送成功")
        smtp_obj.quit()
    except smtplib.SMTPException as e:
        print("Error: 无法发送邮件\n", e)

def checkIsOk(path):
    file_to_check = ['result.out', 'install.log']
    if os.path.exists(os.path.join(path, file_to_check[0])) and os.path.exists(os.path.join(path, file_to_check[1])):
        return True    
    return False
    
def errorReport():
    retStr = f'''<td>NULL</td>
                <td>NULL</td>
                <td>NULL</td>
                <td>NULL</td>
                <td>NULL</td>
            </tr>
            '''
    return retStr + "<p><strong>日构建包或Yat环境出错，请前往蓝区Jenkins任务面板进行排查</strong></p>"
def errorReport_1():
    retStr = f'''<td>NULL</td>
                <td>NULL</td>
                <td>NULL</td>
            </tr>
            '''
    return retStr + "<p><strong>日构建包或Yat环境出错，请前往蓝区Jenkins任务面板进行排查</strong></p>"

def errorReport_2():
    retStr = f'''<td>NULL</td>
                <td>NULL</td>
            </tr>
            '''
    return retStr + "<p><strong>日构建包或Yat环境出错，请前往蓝区Jenkins任务面板进行排查</strong></p>"

if __name__ == '__main__':
    result_path = sys.argv[1]
    if not checkIsOk(result_path):
        data = errorReport()
        finalResult = errorReport_1()
        gaussdb_v = errorReport_2()
    else:
        data = bulidTable(result_path)
        finalResult = bulidTableNum(result_path)
        gaussdb_v = get_install_last_line(result_path)
    mail_msg = generateHTML(data,finalResult,gaussdb_v)
    with open(result_path + "/mail_msg.html", 'w') as f:
        f.write(mail_msg)
    sendEmail(result_path)

